<?php
/**
 * WpConnectSql.php
 * 
 * This file contains the abstract WpConnectSql class which provides access functions for sql databases
 * @author Robert Schmidt <robert@wildpack.de>
 * @version 1.0
 * @package wildpack
 * @subpackage dataaccess.wpconnect
 */

/**
 * The IWpConnect Interface to implement
 */
require_once(dirname(__FILE__).'/IWpConnect.php');

/**
 * The WpConnectBase class to extend
 */
require_once(dirname(__FILE__).'/WpConnectBase.php');

/**
 * An abstract class containing sql functions
 * 
 * The WpConnectSql class provides access functions for sql databases
 * @author Robert Schmidt <robert@wildpack.de>
 * @version 1.0
 * @package wildpack
 * @subpackage dataaccess.wpconnect
 * @abstract
 */
abstract class WpConnectSql extends WpConnectBase implements IWpConnect
{
	protected $LimitTemplate = '';		//If limiting is supported, the limit template is provided here like "LIMIT {start}, {limit}"
	
	/** 
	 * Create a database delete query for SQL databases
	 * @param string $table The table to delete data from
	 * @param string $condition The condition to use for removal
	 * @param Integer $limit[optional] The number of datasets to return
	 * @param Integer $start[optional] The start dataset of the datasets to return
	 * @param String $sort[optional] The column(s) to sort the data by (default "")
	 * @param String $dir[optional] The sort direction (ASC or DESC) (default "ASC")
	 * @return The delete query or FALSE on error
	 */
	protected function getSqlQueryForDelete($table, $condition, $sort='', $dir='', $limit=0, $start=0)
	{
		$strLocation = basename(__FILE__).'/'.__METHOD__;
		
		if ($table === '') {
			$this->writeToLog($strLocation, '', '', '', '20109', array('No table defined'));
			return false;
		}
		
		$strLimit = $this->getSqlSnippetForLimit($limit, $start);
		$strOrder = $this->getSqlSnippetForOrder($sort, $dir);
		$strQuery = 'DELETE FROM '.$table.' '.$condition.' '.$strOrder.' '.$strLimit;
		return $strQuery;
	}
	
	/** 
	 * Create a database insert query for SQL databases
	 * @param string $table The table to retrieve data of
	 * @param array $insertion An associative array containing key/value pairs of the data to insert
	 * @return string The insert query or FALSE on error
	 */
	protected function getSqlQueryForInsert($table, $insertion)
	{
		$strLocation = basename(__FILE__).'/'.__METHOD__;
		
		if ($table === '') {
			$this->writeToLog($strLocation, '', '', '', '20109', array('No table defined'));
			return false;
		}
		
		if (!is_array($insertion)) {
			$this->writeToLog($strLocation, '', '', '', '20109', array('No insertion specified or insertion not of type array'));
			return false;
		}
		
		//--- Build insertion string ---
		$strFields = '';
		$strValues = '';
		if (is_array($insertion) && count($insertion) > 0) {
			foreach ($insertion as $strFieldName => $varValue) {
				$strFields .= $strFieldName.',';
				if (is_null($varValue)) {
					$strValues .= 'NULL,';
				} else {
					if(is_float($varValue) || is_int($varValue) || is_double($varValue) || is_long($varValue)) {
						$strValues .= strval($varValue).',';
					} else {
						$strValues .= '\''.strval($varValue).'\',';
					}
				}
			}
			$strFields = substr($strFields, 0, strlen($strFields)-1);
			$strValues = substr($strValues, 0, strlen($strValues)-1);
		}
		$strInsertion = ' ('.$strFields.') VALUES ('.$strValues.')';
		
		$strQuery = 'INSERT INTO '.$table.' '.$strInsertion;
		return $strQuery;
	}
	
	/** 
	 * Create a database select query for SQL databases
	 * @param String $fields The fields to retrieve from table
	 * @param String $table The table to retrieve from database
	 * @param String $condition The condition for selection
	 * @param Integer $limit[optional] The number of datasets to return
	 * @param Integer $start[optional] The start dataset of the datasets to return
	 * @param String $sort[optional] The column(s) to sort the data by (default "")
	 * @param String $dir[optional] The sort direction (ASC or DESC) (default "ASC")
	 * @return The select query or FALSE on error
	 */
	protected function getSqlQueryForSelect($fields, $table, $condition, $sort='', $dir='', $limit=0, $start=0)
	{
		$strLocation = basename(__FILE__).'/'.__METHOD__;
		
		if ($table === '') {
			$this->writeToLog($strLocation, '', '', '', '20109', array('No table defined'));
			return false;
		}
		
		if ($fields === '') {
			$fields = '*';
		}
		
		$strLimit = $this->getSqlSnippetForLimit($limit, $start);
		$strOrder = $this->getSqlSnippetForOrder($sort, $dir);
		$strQuery = 'SELECT '.$fields.' FROM '.$table.' '.$condition.' '.$strOrder.' '.$strLimit;
		return $strQuery;
	}
	
	/** 
	 * Create an update query for SQL databases
	 * @param string $table The table to update datasets of
	 * @param array $updates An associative array containing key/value pairs of the data to update
	 * @param string $condition The condition to use for update
	 * @param Integer $limit[optional] The number of datasets to return
	 * @param Integer $start[optional] The start dataset of the datasets to return
	 * @param String $sort[optional] The column(s) to sort the data by (default "")
	 * @param String $dir[optional] The sort direction (ASC or DESC) (default "ASC")
	 * @return The update query or FALSE on error
	 */
	protected function getSqlQueryForUpdate($table, $updates, $condition, $sort='', $dir='', $limit=0, $start=0)
	{
		$strLocation = basename(__FILE__).'/'.__METHOD__;
		
		if ($table === '') {
			$this->writeToLog($strLocation, '', '', '', '20109', array('No table defined'));
			return false;
		}
		
		if (!is_array($updates)) {
			$this->writeToLog($strLocation, '', '', '', '20109', array('No update array specified or update not of type array'));
			return false;
		}
		
		//--- Build insertion string ---
		$strUpdate = 'SET ';
		foreach ($updates as $strFieldName => $varValue) {
			if(is_float($varValue) || is_int($varValue) || is_double($varValue) || is_long($varValue)) {
				$strUpdate .= $strFieldName.'='.$varValue.',';
			} else {
				$strUpdate .= $strFieldName.'=\''.$varValue.'\',';
			}
		}
		$strUpdate = substr($strUpdate, 0, strlen($strUpdate)-1);
		$strLimit = $this->getSqlSnippetForLimit($limit, $start);
		$strOrder = $this->getSqlSnippetForOrder($sort, $dir);
		$strQuery = 'UPDATE '.$table.' '.$strUpdate.' '.$condition.' '.$strOrder.' '.$strLimit;
		return $strQuery;
	}
	
	/**
	 * Builds the limit part of the query, if limiting is supported. This method uses the static property LimitTemplate and replaces the 
	 * placeholder {limit} and {start} with the designated values.
	 * @param int $limit The limit value
	 * @param int $start The start/offset value
	 * @return A string representing the limiting part of the query (without leading and trailing spaces!)
	 */
	protected function getSqlSnippetForLimit($limit, $start)
	{
		if ($limit < 1) {
			return '';	//Return all datasets
		}
		if (!isset($this->LimitTemplate) || $this->LimitTemplate === '') {
			return '';	//Limiting not supported
		}
		
		$strReturn = str_replace('{start}', strval($start), $this->LimitTemplate);
		$strReturn = str_replace('{limit}', strval($limit), $strReturn);
		return $strReturn;
	}
	
	/**
	 * Builds the order part of the query. This method uses the standard ORDER BY statement and must be overwritten in other cases
	 * @param string $order The columns to sort by, separated by colon
	 * @param string $dir[optional] The sorting direction (ASC or DESC)
	 * @return A string representing the order part of the query (without leading and trailing spaces!)
	 */
	protected function getSqlSnippetForOrder($order, $dir='ASC')
	{
		if (!isset($order) || $order === '') {
			return '';
		}
		
		if ($dir !== 'ASC' && $dir !== 'DESC') {
			$dir = 'ASC';
		}
		
		return 'ORDER BY '.$order.' '.$dir;
	}
	
	/**
	 * The class constructor
	 * @param object $config[optional] The configuration object
	 * @cfg string location The location of the datasource to use @see $Location
	 * @cfg string user	The user to use for datasource authentication
	 * @cfg string password The password to use for authentication
	 * @cfg object log A log object to log to (must implement the interface IWpLog).
	 * If no log object given, the entries are logged to an array which can be retrieved by the Log attribute
	 */
	public function __construct($config = null)
	{
		parent::__construct($config);
	}
}

/* End of file WpConnectSql.php */
/* Location: ./wildpack/dataaccess/wpconnect/WpConnectSql.php */
